package com.ydzt.fanka.server.zwcl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import org.apache.log4j.Logger;

import com.ydzt.fanka.server.domain.AccountInfo;
import com.ydzt.fanka.server.domain.TransDetailInfo;
import com.ydzt.fanka.server.utils.DBHelper;
import com.ydzt.fanka.server.utils.JdbcUtils;

/*
 * 卡账户更新线程
 * */
public class ZwclUpdateAccountServer extends Thread {
	static Logger logger = Logger.getLogger(ZwclUpdateAccountServer.class
			.getName());

	public void run() {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String stmtday = null;
		String month = null;
		// AccountInfo currentAccountInfo = new AccountInfo();

		List<TransDetailInfo> detailList = null;
		@SuppressWarnings("unused")
		String currentCardID = "";
		// long currentCardCounter = 0;
		while (true) {
			try {
				Thread.sleep(1000);
			} catch (InterruptedException e1) {
				logger.error(e1.getMessage());
			}
			
			detailList = new ArrayList<TransDetailInfo>();
			
			try{
				stmtday = DBHelper.getStmtDay();
				month = stmtday.substring(4, 6);
			}catch(Exception e){
				logger.error(e.getMessage());
			}
			// 在明细中查询当前未清分的交易
			String sqlText_all_detail_seq_null = "select card_type,trans_type,upper(t2.cardid) cardid,sam_id,deal_time,begin_amount,amount,equip_id,t2.card_counter card_counter,supplier_id,trans_seq ";
			sqlText_all_detail_seq_null += " from TBL_CENTRE_TRANSACTION_"
					+ month
					+ " t1,(select cardid, min(card_counter) card_counter from TBL_CENTRE_TRANSACTION_"
					+ month
					+ " where trans_seq is null and trans_type = 3  group by cardid) t2 where upper(t1.cardid) = upper(t2.cardid) and t1.card_counter = t2.card_counter and t1.trans_type = 3 and t1.trans_seq is null";
			try {
				conn = JdbcUtils.getConnectionForOracle();
				conn.setAutoCommit(false);
				pstmt = conn.prepareStatement(sqlText_all_detail_seq_null);
				rs = pstmt.executeQuery();
				while (rs.next()) {
					TransDetailInfo currentDetailRecord = new TransDetailInfo();
					currentDetailRecord.setCardType(rs.getInt("card_type"));
					currentDetailRecord.setTransType(rs.getInt("trans_type"));
					currentDetailRecord.setCardid(rs.getString("cardid"));
					currentDetailRecord.setSam_id(rs.getString("sam_id"));
					currentDetailRecord.setDealTime(rs.getString("deal_time"));
					currentDetailRecord.setBeginAmount(rs
							.getLong("begin_amount"));
					currentDetailRecord.setAmount(rs.getLong("amount"));
					currentDetailRecord.setEquip_id(rs.getString("equip_id"));
					currentDetailRecord.setCardCounter(rs
							.getLong("card_counter"));
					currentDetailRecord.setSupplier_id(rs
							.getString("supplier_id"));
					detailList.add(currentDetailRecord);
				}
				conn.commit();
				conn.setAutoCommit(true);
				// logger.info("查询结束进行提交成功");
			} catch (SQLException e) {
				logger.error(e.getMessage());
				try {
					if (conn != null) {
						conn.rollback();
						conn.setAutoCommit(true);
					}
				} catch (SQLException e2) {
					logger.error(e2.getMessage());
				}
			} finally {
				JdbcUtils.freeAll(rs, pstmt, conn);
				// logger.info("释放oracle连接");
			}

			if (detailList.size() == 0) {
				// logger.info("查询到0条明细需要进行更新");
				try {
					Thread.sleep(5000);
				} catch (InterruptedException e) {
				}
			} else {
				// logger.info("查询到有" + detailList.size() + "明细要进行更新");
				// 批量更新卡账户信息
				for (TransDetailInfo tdi : detailList) {
					long deal_amount = tdi.getAmount();
					long newQbAmount = tdi.getBeginAmount() - tdi.getAmount();
					String dubious_type = "";
					/*
					 * if(currentDetailRecord.getBeginAmount() !=
					 * currentAccountInfo.getQbAmount()){ dubious_type = "D2"; }
					 */
					String newDealTime = tdi.getDealTime();
					// int newTransType = currentDetailRecord.getTransType();
					// 对卡账户进行更新
					boolean flag = updateAccountInfoConsume(tdi.getCardid()
							.toUpperCase(), newQbAmount, tdi.getCardCounter(),
							newDealTime, 3, deal_amount);
					if (flag) {// 为此条明细加上trans_seq
						// long seq = getTransSeq();
						long seq = 1;
						updateDetailAboutSeq(tdi.getCardid().toUpperCase(),
								tdi.getCardCounter(), 3, seq, month,
								dubious_type);
					}
				}
				detailList.clear();
			}
		}
	}

	@SuppressWarnings("unused")
	private static String getMadStr(String str) {
		StringBuilder sb = new StringBuilder();
		char[] strArray = new char[str.length()];
		strArray = str.toCharArray();
		for (int i = str.length() - 1; i > 0; i--) {
			sb.append(strArray[i - 1]);
			sb.append(strArray[i]);
			// sb.append(str.substring(i-1, 1));
			// sb.append(str.substring(i, 1));
			i--;
		}
		String sbStr = sb.toString();
		sb.delete(0, sb.length());
		return sbStr;
	}

	/*
	 * 更新重复上传的数据为可疑交易
	 */
	@SuppressWarnings("unused")
	private static boolean updateRepDubiouInDetail(String cardid,
			long cardCounter, String dubiou_type, String month, long tran_seq) {
		boolean flag = false;
		Connection conn = null;
		Statement pstmt = null;
		ResultSet rs = null;
		// String updateInfoSql = "update TBL_CENTRE_TRANSACTION_" + month +
		// " set TRANS_SEQ = ?,DUBIOUS_TYPE = ? where upper(CARDID) = ? and TRANS_TYPE = ? and CARD_COUNTER = ? and trans_seq is null and rownum = 1";
		String updateInfoSql = "update TBL_CENTRE_TRANSACTION_"
				+ month
				+ " set TRANS_SEQ = "
				+ tran_seq
				+ ",DUBIOUS_TYPE = '"
				+ dubiou_type
				+ "' where STMT_DAY = (select STMT_DAY from tbl_centre_clearday) and upper(CARDID) = '"
				+ cardid + "' and TRANS_TYPE = 3 and CARD_COUNTER = "
				+ cardCounter + " and trans_seq is null and rownum = 1";
		try {
			conn = JdbcUtils.getConnectionForOracle();
			conn.setAutoCommit(false);
			// pstmt = conn.prepareStatement(updateInfoSql);
			pstmt = conn.createStatement();
			/*
			 * pstmt.setLong(1, tran_seq); pstmt.setString(2, dubiou_type);
			 * pstmt.setString(3, getMadStr(cardid)); pstmt.setInt(4, 3);
			 * pstmt.setLong(5,cardCounter);
			 */
			pstmt.executeUpdate(updateInfoSql);
			// pstmt.executeUpdate();
			conn.commit();
			conn.setAutoCommit(true);
			flag = true;
		} catch (Exception e) {
			logger.error("标记此明细记录为可疑(D1)出错!" + e.getMessage());
			try {
				if (conn != null) {
					conn.rollback();
					conn.setAutoCommit(true);
				}
			} catch (SQLException e2) {
			}
			flag = false;
		} finally {
			JdbcUtils.freeAll(rs, pstmt, conn);
		}
		return flag;
	}

	@SuppressWarnings("unused")
	private static long getMinCardCounterByCardid(String cardid,
			String stmtday, String month) {
		long cardCounter = -1;
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql_min_card_counter = "select min(card_counter) card_counter from TBL_CENTRE_TRANSACTION_"
				+ month
				+ " where and upper(cardid) = ? trans_seq is null and stmt_day = ?";
		try {
			conn = JdbcUtils.getConnectionForOracle();
			conn.setAutoCommit(false);
			pstmt = conn.prepareStatement(sql_min_card_counter);
			pstmt.setString(1, cardid);
			pstmt.setString(2, stmtday);
			rs = pstmt.executeQuery();
			while (rs.next()) {
				cardCounter = rs.getLong("card_counter");
			}
			conn.commit();
			conn.setAutoCommit(true);
		} catch (SQLException e) {
			try {
				if (conn != null) {
					conn.rollback();
					conn.setAutoCommit(true);
				}
			} catch (SQLException e2) {
			}
		} finally {
			JdbcUtils.freeAll(rs, pstmt, conn);
		}
		return cardCounter;
	}

	@SuppressWarnings("unused")
	private static boolean updateAccountCardCounter(String cardid,
			long cardCounter) {
		boolean flag = false;
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String updateInfoSql = "update tbl_centre_account set CARD_COUNTER = ?  where ACCOUNT_CARDID = ?";
		try {
			conn = JdbcUtils.getConnectionForOracle();
			conn.setAutoCommit(false);
			pstmt = conn.prepareStatement(updateInfoSql);
			pstmt.setLong(1, cardCounter);
			pstmt.setString(2, cardid);
			pstmt.executeUpdate();
			conn.commit();
			conn.setAutoCommit(true);
			flag = true;
		} catch (Exception e) {
			try {
				if (conn != null) {
					conn.rollback();
					conn.setAutoCommit(true);
				}
			} catch (SQLException e2) {
			}
			flag = false;
		} finally {
			JdbcUtils.freeAll(rs, pstmt, conn);
		}
		return flag;
	}

	private static boolean updateDetailAboutSeq(String cardid,
			long cardCounter, int trans_type, long tran_seq, String month,
			String dubious_type) {
		logger.info("调用updateDetailAboutSeq 分配seq");
		boolean flag = false;
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String updateInfoSql = "update TBL_CENTRE_TRANSACTION_"
				+ month
				+ " set TRANS_SEQ = ?,DUBIOUS_TYPE = ? where upper(CARDID) = ? and TRANS_TYPE = ? and CARD_COUNTER = ?";
		try {
			conn = JdbcUtils.getConnectionForOracle();
			conn.setAutoCommit(false);
			pstmt = conn.prepareStatement(updateInfoSql);
			pstmt.setLong(1, tran_seq);
			pstmt.setString(2, dubious_type);
			pstmt.setString(3, cardid);
			pstmt.setInt(4, trans_type);
			pstmt.setLong(5, cardCounter);
			pstmt.executeUpdate();
			conn.commit();
			conn.setAutoCommit(true);
			flag = true;
			logger.info("为卡号" + cardid + "更新seq成功 改笔明细已经进行卡账户更新操作");
		} catch (Exception e) {
			logger.error("更新明细序列号出错!" + e.getMessage());
			try {
				if (conn != null) {
					conn.rollback();
					conn.setAutoCommit(true);
				}
			} catch (SQLException e2) {
			}
			flag = false;
		} finally {
			JdbcUtils.freeAll(rs, pstmt, conn);
		}
		return flag;
	}

	@SuppressWarnings("unused")
	private static boolean updateAccountInfo(String cardid, long qbAmount,
			String dealTime, int transType, long deal_amount) {
		boolean flag = false;
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String updateInfoSql = "update tbl_centre_account set QB_AMOUNT = ?,DEAL_AMOUNT = ?,DEAL_TIME = ?,TRANS_TYPE = ? where upper(ACCOUNT_CARDID) = ?";
		try {
			conn = JdbcUtils.getConnectionForOracle();
			conn.setAutoCommit(false);
			pstmt = conn.prepareStatement(updateInfoSql);
			pstmt.setLong(1, qbAmount);
			pstmt.setLong(2, deal_amount);
			pstmt.setString(3, dealTime);
			pstmt.setInt(4, transType);
			pstmt.setString(5, cardid);
			pstmt.executeUpdate();
			conn.commit();
			conn.setAutoCommit(true);
			flag = true;
		} catch (Exception e) {
			try {
				if (conn != null) {
					conn.rollback();
					conn.setAutoCommit(true);
				}
			} catch (SQLException e2) {
			}
			flag = false;
		} finally {
			JdbcUtils.freeAll(rs, pstmt, conn);
		}
		return flag;
	}

	private static boolean updateAccountInfoConsume(String cardid,
			long qbAmount, long cardCounter, String dealTime, int transType,
			long deal_amount) {
		logger.info("调用updateAccountInfoConsume 更新卡账户");
		boolean flag = false;
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String updateInfoSql = "update tbl_centre_account set QB_AMOUNT = ?,DEAL_AMOUNT = ?,CARD_COUNTER = ?,DEAL_TIME = ?,TRANS_TYPE = ? where upper(ACCOUNT_CARDID) = ?";
		try {
			conn = JdbcUtils.getConnectionForOracle();
			conn.setAutoCommit(false);
			pstmt = conn.prepareStatement(updateInfoSql);
			pstmt.setLong(1, qbAmount);
			pstmt.setLong(2, deal_amount);
			pstmt.setLong(3, cardCounter);
			pstmt.setString(4, dealTime);
			pstmt.setInt(5, transType);
			pstmt.setString(6, cardid);
			pstmt.executeUpdate();
			conn.commit();
			conn.setAutoCommit(true);
			flag = true;
			logger.info("对" + cardid + "更新卡账户信息" + " qb_amount更新为" + qbAmount
					+ "卡计数器更新为" + cardCounter + "最后一笔交易金额更新为" + deal_amount);
		} catch (Exception e) {
			logger.error("对" + cardid + "更新卡账户信息失败" + e.getMessage());
			try {
				if (conn != null) {
					conn.rollback();
					conn.setAutoCommit(true);
				}
			} catch (SQLException e2) {
			}
			flag = false;

		} finally {
			JdbcUtils.freeAll(rs, pstmt, conn);
		}
		return flag;
	}

	@SuppressWarnings("unused")
	private static AccountInfo getAccountInfoByCardID(String cardid) {
		AccountInfo accountInfo = new AccountInfo();
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sqlText = "select ACCOUNT_WORKNO,ACCOUNT_CARDID,ACCOUNT_DSFID,CARD_TYPE,CARD_STATE,QB_AMOUNT,DEAL_AMOUNT,CARD_COUNTER,DEAL_TIME,TRANS_TYPE from tbl_centre_account where upper(ACCOUNT_CARDID) = ?";
		try {
			conn = JdbcUtils.getConnectionForOracle();
			conn.setAutoCommit(false);
			pstmt = conn.prepareStatement(sqlText);
			pstmt.setString(1, cardid);
			rs = pstmt.executeQuery();
			while (rs.next()) {
				accountInfo.setAccountWorkNo(rs.getString("ACCOUNT_WORKNO"));
				accountInfo.setAccountCardID(rs.getString("ACCOUNT_CARDID")
						.toUpperCase());
				accountInfo.setAccountDsfID(rs.getString("ACCOUNT_DSFID"));
				accountInfo.setCardType(rs.getInt("CARD_TYPE"));
				accountInfo.setCardState(rs.getInt("CARD_STATE"));
				accountInfo.setQbAmount(rs.getLong("QB_AMOUNT"));
				accountInfo.setDealAmount(rs.getLong("DEAL_AMOUNT"));
				accountInfo.setCardCounter(rs.getInt("CARD_COUNTER"));
				accountInfo.setDealTime(rs.getString("DEAL_TIME"));
			}
			conn.commit();
			conn.setAutoCommit(true);
		} catch (SQLException e) {
			logger.error("为明细分配序号出错:" + e.getMessage());
			try {
				if (conn != null) {
					conn.rollback();
					conn.setAutoCommit(true);
				}
			} catch (SQLException e2) {
			}
		} finally {
			JdbcUtils.freeAll(rs, pstmt, conn);
		}
		return accountInfo;
	}

	@SuppressWarnings("unused")
	private static long getTransSeq() {
		String sqlText = "select seq_trans_year.nextval seq from dual";
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		long seq = 0;
		try {
			conn = JdbcUtils.getConnectionForOracle();
			conn.setAutoCommit(false);
			pstmt = conn.prepareStatement(sqlText);
			rs = pstmt.executeQuery();
			while (rs.next()) {
				seq = rs.getLong("seq");
			}
			conn.commit();
			conn.setAutoCommit(true);
		} catch (SQLException e) {
			logger.error("为明细分配序号出错:" + e.getMessage());
			try {
				if (conn != null) {
					conn.rollback();
					conn.setAutoCommit(true);
				}
			} catch (SQLException e2) {
			}
		} finally {
			JdbcUtils.freeAll(rs, pstmt, conn);
		}
		return seq;
	}

}
